package dao.entity;

import bean.Comment;
import dao.MysqlConfiguration;
import dao.interf.CommentDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class CommentDaoImpl implements CommentDao {
    @Override
    public int addComment(Comment c) {
        //连接数据库
        Connection conn = MysqlConfiguration.MysqlConnection();
        //定义sql语句
        String sql = "insert into c_comments values(?,?,?,?,?,?)";

        int n = 0;//受影响的行数
        try {
            //创建statement语句对象
            //Statement st = conn.createStatement();
            //创建一个PrepareStatement语句对象
            PreparedStatement pst = conn.prepareStatement(sql);
            System.out.println("创建语句对象成功!");
            pst.setString(1,c.getCcid());
            pst.setString(2,c.getCid());
            pst.setString(3,c.getGid());
            pst.setString(4,c.getCname());
            pst.setString(5,c.getCc_time());
            pst.setString(6,c.getContent());
            n = pst.executeUpdate();//执行语句
            System.out.println("执行语句成功!");
            //执行指定的sql语句
            //n = pst.executeUpdate();
        } catch (Exception e) {
            System.out.println("创建语句对象失败!"+e.getMessage());
        }
        return n;//返回受影响的行数
    }

    @Override
    public int updateComment(Comment c) {
        Connection conn = MysqlConfiguration.MysqlConnection();
        String sql="update c_comments set cc_time=? , content=? where ccid=?;";
        int n = 0;
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1,c.getCc_time());
            pst.setString(2,c.getContent());
            pst.setString(3,c.getCcid());
            n = pst.executeUpdate();
        } catch (Exception e) {
            System.out.println("错误!!"+e.getMessage());
        }
        return n;
    }

    @Override
    public int selectCcidmaxComment() {
        //连接数据库
        String ccid = "";
        int num = 0;
        Connection conn = MysqlConfiguration.MysqlConnection();
        String sql = "select  max(ccid) from c_comments;";
        ResultSet rs = null;
        try {
            Statement st = conn.createStatement();
            System.out.println("查询评论语句创建成功!");
            rs = st.executeQuery(sql);
            if(rs!=null){
                System.out.println("查找成功");
                rs.next();//移动光标
                System.out.println("光标移动成功");
                ccid = rs.getString(1);
                System.out.println("查询成功,ccid为"+ccid);
                ccid = ccid.substring(2);
                num = Integer.parseInt(ccid);
                System.out.println("获取成功,ccid序号为"+ num);
            }
        } catch (Exception e) {
            System.out.println("创建语句对象失败!"+e.getMessage());
        }
            return num;
    }

    @Override
    public ResultSet queryComment() {
        //连接数据库
        Connection conn = MysqlConfiguration.MysqlConnection();
        String sql = "select * from c_comments;";
        ResultSet rs = null;
        try {
            Statement st = conn.createStatement();
            System.out.println("查询评论语句创建成功!");
            rs = st.executeQuery(sql);
        } catch (Exception e) {
            System.out.println("创建语句对象失败!"+e.getMessage());
        }
        return rs;
    }

    @Override
    public List<Comment> comment_find(int currentPage) {
        //连接数据库
        Connection conn = MysqlConfiguration.MysqlConnection();
        String sql = "select * from c_comments limit ?,?;";
        ResultSet rs;
        List<Comment> list = new ArrayList<Comment>();
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1,(currentPage-1)*5);
            pst.setInt(2,5);
            rs = pst.executeQuery();
            while (rs.next()){
                Comment c = new Comment();
                c.setCcid(rs.getString(1));
                c.setCid(rs.getString(2));
                c.setGid(rs.getString(3));
                c.setCname(rs.getString(4));
                c.setCc_time(rs.getString(5));
                c.setContent(rs.getString(6));
                list.add(c);//添加当前学生到列表中
            }
        } catch (Exception e) {
            System.out.println("创建语句对象失败!"+e.getMessage());
        }
        return list;
    }

    @Override
    public int comment_count() {
        //连接数据库
        Connection conn = MysqlConfiguration.MysqlConnection();
        String sql = "select count(*) from c_comments;";
        ResultSet rs;
        int n = 0;
        try {
            Statement st = conn.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()){
                n = rs.getInt(1);
            }
        } catch (Exception e) {
            System.out.println("创建语句对象失败! 错误是:"+e.getMessage());
        }
        return n;
    }

    @Override
    public int deleteComment(String ccid) {
        //连接数据库
        Connection conn = MysqlConfiguration.MysqlConnection();
        //定义sql语句
        String sql = "delete from c_comments where ccid=?";
        int n = 0;//初始化受影响的行数
        try {
            //定义预处理语句对象
            PreparedStatement pst = conn.prepareStatement(sql);
            System.out.println("创建语句对象成功");
            pst.setString(1,ccid);
            //pst.setInt(1,sno);
            n = pst.executeUpdate();//执行语句
            //System.out.println("执行语句成功");
        } catch (Exception e) {
            System.out.println("创建语句异常: "+e.getMessage());
        }
        return n;
    }

    @Override
    public ResultSet queryCommentByCcid(String ccid) {
        Connection conn = MysqlConfiguration.MysqlConnection();
        String sql = "select * from c_comments where ccid=?;";
        ResultSet rs = null;
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1,ccid);
            rs = pst.executeQuery();
        } catch (Exception e) {
            System.out.println("错误!"+e.getMessage());
        }
        return rs;
    }

}
